﻿/*
 CreaMotion TeAm
 http://www.creamotion.net/
 
 Developer : Caner Sarıgül
 Region : Turkey
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NhibernateClassBuilder;

namespace CreaMotion.Connectors
{
    public class MssqlConnector : CreaMotion.Interfaces.IDBConnector
    {
        #region Db2NetType

        Dictionary<String, Type> Db2NetType = new Dictionary<string, Type>() { {"varchar", typeof(string) },
                                                                               {"nvarchar", typeof(string)},
                                                                               {"bigint",typeof(Int64)},
                                                                               {"binary",typeof(byte)},
                                                                               {"bit",typeof(bool)},
                                                                               {"char",typeof(char)},
                                                                               {"date",typeof(DateTime)},
                                                                               {"datetime",typeof(DateTime)},
                                                                               {"datetime2",typeof(DateTime)},
                                                                               {"datetimeoffset",typeof(DateTimeOffset)},
                                                                               {"decimal",typeof(decimal)},
                                                                               {"float",typeof(float)},
                                                                               {"image",typeof(byte[])},
                                                                               {"nchar",typeof(char)},
                                                                               {"ntext",typeof(string)},
                                                                               {"numeric",typeof(int)},
                                                                               {"text",typeof(string)},
                                                                               {"xml",typeof(string)},
                                                                               {"int",typeof(int)},
                                                                               {"smallint",typeof(Int16)},
                                                                               {"tinyint",typeof(Int16)},
                                                                               {"uniqueidentifier",typeof(string)}
                                                                             };

        #endregion

        #region Connect

        public Interfaces.Database Connect()
        {
            Interfaces.Database ReturnData = new Interfaces.Database();
            
            NHibernate.ISQLQuery data = ConnectorManager.CurrentNHibernateSession.CreateSQLQuery(@"Select 'Id' = ROW_NUMBER() Over(Order By TABLE_CATALOG) , TABLE_CATALOG,TABLE_NAME,
                                                                                                   COLUMN_NAME,DATA_TYPE,COLUMN_DEFAULT,COLLATION_NAME,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH
                                                                                                   From INFORMATION_SCHEMA.COLUMNS");

            NHibernate.ISQLQuery PkData = ConnectorManager.CurrentNHibernateSession.CreateSQLQuery(@"SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
                                                                                                     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
                                                                                                     WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME");

            data.AddEntity(typeof(MssqlColumns));
            PkData.AddEntity(typeof(Pk));

            IList<MssqlColumns> DatabaseSchema = data.List<MssqlColumns>();
            IList<Pk> PrimaryKeys = PkData.List<Pk>();

            List<String> TablesNames = (from tmp in DatabaseSchema
                                        select tmp.TABLE_NAME).Distinct().ToList();

            List<Interfaces.Table> TempTables = new List<Interfaces.Table>();
            foreach (String TableName in TablesNames)
            {
                Interfaces.Table TempTable = new Interfaces.Table();
                List<String> FieldNames = (from tmp in DatabaseSchema
                                           where tmp.TABLE_NAME == TableName
                                           select tmp.COLUMN_NAME).ToList();

                List<Interfaces.Field> TempFields = new List<Interfaces.Field>();
                foreach (String Field in FieldNames)
                {
                    Interfaces.Field tempField = new Interfaces.Field();
                    MssqlColumns tempColumn = (from tmp in DatabaseSchema 
                                               where tmp.TABLE_NAME == TableName &&
                                                     tmp.COLUMN_NAME == Field
                                               select tmp).First();

                    Pk pk = (from tmp in PrimaryKeys
                             where tmp.TABLE_NAME == TableName && tmp.COLUMN_NAME == Field
                             select tmp).FirstOrDefault();

                    if (pk != null)
                    {
                        tempField.IsPrimaryKey = true;
                        
                        tempField.Indexes = new List<Interfaces.Index>();
                        tempField.Indexes.Add(new Interfaces.Index() { IndexType = "Primary Key", IndexName = pk.CONSTRAINT_NAME });
                    }

                    tempField.FieldName = tempColumn.COLUMN_NAME; 
                    tempField.DefaultValue = tempColumn.COLUMN_DEFAULT;
                    tempField.Collation = tempColumn.COLLATION_NAME;
                    tempField.NotNull = (tempColumn.IS_NULLABLE=="YES"? true : false);
                    tempField.ValueLength = tempColumn.CHARACTER_MAXIMUM_LENGTH;
                    tempField.FieldType_db = tempColumn.DATA_TYPE;
                    try { tempField.FieldType_net = Db2NetType[tempColumn.DATA_TYPE]; }
                    catch { };
                    
                    TempFields.Add(tempField);
                }

                TempTable.TableName = TableName;
                TempTable.Fields = TempFields;

                TempTables.Add(TempTable);
            }

            ReturnData.DatabaseName = DatabaseSchema.First().TABLE_CATALOG;
            ReturnData.Engine = "MDB";
            ReturnData.Tables = TempTables;

            return ReturnData;
        }
        
        #endregion

        #region About Function

        public Interfaces.About AboutConnector()
        {
            Interfaces.About inf = new Interfaces.About();

            inf.Description = "Microsoft Sql Server Connector";
            inf.Developer = "CreaMotion Team";
            inf.SupportedDatabaseSystem = "Mssql";
            inf.Version = "1.0.0";

            return inf;
        }
        #endregion
    }
}
